package com.ais.gaea.dialect;

import com.ais.gaea.AnnotationUtils;
import com.ais.gaea.ColumnType;
import com.ais.gaea.annotation.TableColumn;
import com.ais.gaea.constant.Constants;
import org.springframework.util.Assert;

import java.util.Collections;
import java.util.HashSet;
import java.util.Set;

/**
 * @author xuning
 * @date 2022/11/26
 **/
public class MySQLInstance extends DBInstance {

    private static final Set<String> RESERVED_WORDS;

    static {
        Set<String> words = new HashSet<>();
        words.add("ACCESSIBLE");
        words.add("ADD");
        words.add("ALL");
        words.add("ALTER");
        words.add("ANALYZE");
        words.add("AND");
        words.add("AS");
        words.add("ASC");
        words.add("ASENSITIVE");
        words.add("BEFORE");
        words.add("BETWEEN");
        words.add("BIGINT");
        words.add("BINARY");
        words.add("BLOB");
        words.add("BOTH");
        words.add("BY");
        words.add("CALL");
        words.add("CASCADE");
        words.add("CASE");
        words.add("CHANGE");
        words.add("CHAR");
        words.add("CHARACTER");
        words.add("CHECK");
        words.add("COLLATE");
        words.add("COLUMN");
        words.add("CONDITION");
        words.add("CONSTRAINT");
        words.add("CONTINUE");
        words.add("CONVERT");
        words.add("CREATE");
        words.add("CROSS");
        words.add("CURRENT_DATE");
        words.add("CURRENT_TIME");
        words.add("CURRENT_TIMESTAMP");
        words.add("CURRENT_USER");
        words.add("CURSOR");
        words.add("DATABASE");
        words.add("DATABASES");
        words.add("DAY_HOUR");
        words.add("DAY_MICROSECOND");
        words.add("DAY_MINUTE");
        words.add("DAY_SECOND");
        words.add("DEC");
        words.add("DECIMAL");
        words.add("DECLARE");
        words.add("DEFAULT");
        words.add("DELAYED");
        words.add("DELETE");
        words.add("DESC");
        words.add("DESCRIBE");
        words.add("DETERMINISTIC");
        words.add("DISTINCT");
        words.add("DISTINCTROW");
        words.add("DIV");
        words.add("DOUBLE");
        words.add("DROP");
        words.add("DUAL");
        words.add("EACH");
        words.add("ELSE");
        words.add("ELSEIF");
        words.add("ENCLOSED");
        words.add("ESCAPED");
        words.add("EXISTS");
        words.add("EXIT");
        words.add("EXPLAIN");
        words.add("FALSE");
        words.add("FETCH");
        words.add("FLOAT");
        words.add("FLOAT4");
        words.add("FLOAT8");
        words.add("FOR");
        words.add("FORCE");
        words.add("FOREIGN");
        words.add("FROM");
        words.add("FULLTEXT");
        words.add("GRANT");
        words.add("GROUP");
        words.add("HAVING");
        words.add("HIGH_PRIORITY");
        words.add("HOUR_MICROSECOND");
        words.add("HOUR_MINUTE");
        words.add("HOUR_SECOND");
        words.add("IF");
        words.add("IGNORE");
        words.add("IN");
        words.add("INDEX");
        words.add("INFILE");
        words.add("INNER");
        words.add("INOUT");
        words.add("INSENSITIVE");
        words.add("INSERT");
        words.add("INT");
        words.add("INT1");
        words.add("INT2");
        words.add("INT3");
        words.add("INT4");
        words.add("INT8");
        words.add("INTEGER");
        words.add("INTERVAL");
        words.add("INTO");
        words.add("IS");
        words.add("ITERATE");
        words.add("JOIN");
        words.add("KEY");
        words.add("KEYS");
        words.add("KILL");
        words.add("LEADING");
        words.add("LEAVE");
        words.add("LEFT");
        words.add("LIKE");
        words.add("LIMIT");
        words.add("LINEAR");
        words.add("LINES");
        words.add("LOAD");
        words.add("LOCALTIME");
        words.add("LOCALTIMESTAMP");
        words.add("LOCK");
        words.add("LONG");
        words.add("LONGBLOB");
        words.add("LONGTEXT");
        words.add("LOOP");
        words.add("LOW_PRIORITY");
        words.add("MASTER_SSL_VERIFY_SERVER_CERT");
        words.add("MATCH");
        words.add("MAXVALUE");
        words.add("MEDIUMBLOB");
        words.add("MEDIUMINT");
        words.add("MEDIUMTEXT");
        words.add("MIDDLEINT");
        words.add("MINUTE_MICROSECOND");
        words.add("MINUTE_SECOND");
        words.add("MOD");
        words.add("MODIFIES");
        words.add("NATURAL");
        words.add("NOT");
        words.add("NO_WRITE_TO_BINLOG");
        words.add("NULL");
        words.add("NUMERIC");
        words.add("ON");
        words.add("OPTIMIZE");
        words.add("OPTION");
        words.add("OPTIONALLY");
        words.add("OR");
        words.add("ORDER");
        words.add("OUT");
        words.add("OUTER");
        words.add("OUTFILE");
        words.add("PRECISION");
        words.add("PRIMARY");
        words.add("PROCEDURE");
        words.add("PURGE");
        words.add("RANGE");
        words.add("READ");
        words.add("READS");
        words.add("READ_WRITE");
        words.add("REAL");
        words.add("REFERENCES");
        words.add("REGEXP");
        words.add("RELEASE");
        words.add("RENAME");
        words.add("REPEAT");
        words.add("REPLACE");
        words.add("REQUIRE");
        words.add("RESIGNAL");
        words.add("RESTRICT");
        words.add("RETURN");
        words.add("REVOKE");
        words.add("RIGHT");
        words.add("RLIKE");
        words.add("SCHEMA");
        words.add("SCHEMAS");
        words.add("SECOND_MICROSECOND");
        words.add("SELECT");
        words.add("SENSITIVE");
        words.add("SEPARATOR");
        words.add("SET");
        words.add("SHOW");
        words.add("SIGNAL");
        words.add("SMALLINT");
        words.add("SPATIAL");
        words.add("SPECIFIC");
        words.add("SQL");
        words.add("SQLEXCEPTION");
        words.add("SQLSTATE");
        words.add("SQLWARNING");
        words.add("SQL_BIG_RESULT");
        words.add("SQL_CALC_FOUND_ROWS");
        words.add("SQL_SMALL_RESULT");
        words.add("SSL");
        words.add("STARTING");
        words.add("STRAIGHT_JOIN");
        words.add("TABLE");
        words.add("TERMINATED");
        words.add("THEN");
        words.add("TINYBLOB");
        words.add("TINYINT");
        words.add("TINYTEXT");
        words.add("TO");
        words.add("TRAILING");
        words.add("TRIGGER");
        words.add("TRUE");
        words.add("UNDO");
        words.add("UNION");
        words.add("UNIQUE");
        words.add("UNLOCK");
        words.add("UNSIGNED");
        words.add("UPDATE");
        words.add("USAGE");
        words.add("USE");
        words.add("USING");
        words.add("UTC_DATE");
        words.add("UTC_TIME");
        words.add("UTC_TIMESTAMP");
        words.add("VALUES");
        words.add("VARBINARY");
        words.add("VARCHAR");
        words.add("VARCHARACTER");
        words.add("VARYING");
        words.add("WHEN");
        words.add("WHERE");
        words.add("WHILE");
        words.add("WITH");
        words.add("WRITE");
        words.add("XOR");
        words.add("YEAR_MONTH");
        words.add("ZEROFILL");
        RESERVED_WORDS = Collections.unmodifiableSet(words);
    }


    @Override
    protected String tableOptions(Class<?> table) {
        return "ENGINE=Innodb COMMENT='" + AnnotationUtils.getTableComment(table) + "'";
    }

    @Override
    protected String getIdentitySql(TableColumn column) {
        return " auto_increment";
    }

    @Override
    protected String getColumnTypeSql(TableColumn column) {
        StringBuilder sb = new StringBuilder();
        ColumnType type = column.type();
        switch (type) {
            case TINYINT:
            case SMALLINT:
            case INT:
            case BIGINT:
            case DATE:
                return type.toString().toLowerCase();
            case TINYINT_UNSIGNED:
                return "tinyint unsigned";
            case INT_UNSIGNED:
                return "int unsigned";
            case BIGINT_UNSIGNED:
                return "bigint unsigned";
            case DECIMAL:
                sb.append("decimal(");
                sb.append(column.precision());
                sb.append(",");
                sb.append(column.scale());
                sb.append(")");
                break;
            case DATETIME:
                sb.append("datetime(");
                sb.append(column.scale());
                sb.append(")");
                break;
            case TIME:
                sb.append("time(");
                sb.append(column.scale());
                sb.append(")");
                break;
            case VARCHAR:
                sb.append("varchar(");
                sb.append(column.length());
                sb.append(") CHARSET utf8 COLLATE utf8_bin");
                break;
            case TEXT:
                return "text CHARSET utf8 COLLATE utf8_bin";
            default:
                //unexpected type
        }
        return sb.toString();
    }

    @Override
    protected String getColumnCommentSql(TableColumn column) {
        return " COMMENT '" + column.comment() + "'";
    }

    @Override
    public String getExistTableSql(String schemaName) {
        return "SELECT TABLE_NAME FROM information_schema.TABLES WHERE table_schema ='" + schemaName + "'";
    }

    @Override
    public String getColumnAttributeSql(String schemaName, String tableName) {
        return "DESC " + tableName;
    }

    @Override
    public String getField() {
        return "Field";
    }

    @Override
    public String getType() {
        return "Type";
    }

    @Override
    public String getLength() {
        return "Type";
    }

    @Override
    public String getPrecision() {
        return "Type";
    }

    @Override
    public String getScale() {
        return "Type";
    }

    @Override
    public String getNullable() {
        return "Null";
    }

    @Override
    public String getDefaultValue() {
        return "Default";
    }

    @Override
    public boolean convertNullable(String nullable) {
        return Constants.YES.equalsIgnoreCase(nullable);
    }


    @Override
    public String convertType(String type) {
        Assert.notNull(type, "type must not be NULL");
        if (type.contains(Constants.LEFT_BRACKET)) {
            String part1 = type.split(Constants.ESCAPE + Constants.LEFT_BRACKET)[0];
            String[] split = type.split(Constants.ESCAPE + Constants.RIGHT_BRACKET);
            String part2 = split.length > 1 ? split[1] : Constants.EMPTY_STRING;
            type = (part1 + " " + part2).trim();
        }
        return type;
    }

    @Override
    public String convertLength(String type) {
        Assert.notNull(type, "type must not be NULL");
        String columnType = convertType(type);
        String length = Constants.NO_MEANING;
        //只给varchar类型的转换, 其他类型默认给0,不使用
        if (ColumnType.VARCHAR.toString().equalsIgnoreCase(columnType)) {
            length = type.split(Constants.ESCAPE + Constants.LEFT_BRACKET)[1]
                    .split(Constants.ESCAPE + Constants.RIGHT_BRACKET)[0];
        }
        return length;
    }

    @Override
    public String convertPrecision(String type) {
        Assert.notNull(type, "type must not be NULL");
        String columnType = convertType(type);
        String precision = Constants.NO_MEANING;
        // 只给decimal类型转换
        if (ColumnType.DECIMAL.toString().equalsIgnoreCase(columnType)) {
            precision = type.split(Constants.ESCAPE + Constants.LEFT_BRACKET)[1]
                    .split(Constants.ESCAPE + Constants.RIGHT_BRACKET)[0]
                    .split(Constants.COMMA)[0];

        }
        return precision;
    }

    @Override
    public String convertScale(String type) {
        Assert.notNull(type, "type must not be NULL");
        String columnType = convertType(type);
        String scale = Constants.NO_MEANING;
        if (ColumnType.DECIMAL.toString().equalsIgnoreCase(columnType)) {
            scale = type.split(Constants.ESCAPE + Constants.LEFT_BRACKET)[1]
                    .split(Constants.ESCAPE + Constants.RIGHT_BRACKET)[0]
                    .split(Constants.COMMA)[1];
        } else if (ColumnType.DATETIME.toString().equalsIgnoreCase(columnType)
                || ColumnType.TIME.toString().equalsIgnoreCase(columnType)) {
            scale = columnType.contains(Constants.LEFT_BRACKET) ?
                    type.split(Constants.ESCAPE + Constants.LEFT_BRACKET)[1]
                            .split(Constants.ESCAPE + Constants.RIGHT_BRACKET)[0]
                    :String.valueOf(0);
        }
        return scale;
    }

    @Override
    public String mappingColumnType(ColumnType columnType) {
        switch (columnType) {
            case TINYINT:
            case SMALLINT:
            case INT:
            case BIGINT:
            case DATE:
            case VARCHAR:
            case TEXT:
            case TIME:
            case DATETIME:
            case DECIMAL:
                return columnType.toString().toLowerCase();
            case TINYINT_UNSIGNED:
                return "tinyint unsigned";
            case INT_UNSIGNED:
                return "int unsigned";
            case BIGINT_UNSIGNED:
                return "bigint unsigned";
            default:
                //unexpected type
                return "";
        }
    }

    @Override
    public String escapeCharacter(boolean left) {
        return "`";
    }

    @Override
    public boolean isReservedWord(String columnName) {
        return RESERVED_WORDS.contains(columnName.toUpperCase());
    }

}
